set verify off
set serveroutput on size 1000000;

declare   
                    		  
	l_total_extracted_batch		NUMBER;
	l_total_extracted_formulas 	NUMBER;
	l_total_extracted_flines	NUMBER;
	
	l_total_imported_batch		NUMBER;
	l_total_imported_formulas 	NUMBER;
	l_total_imported_flines		NUMBER;
	l_func_id					VARCHAR2(100):='CAPGLREC_ALLOC';	
	
BEGIN	
   
	select count(1)
	into   l_total_extracted_batch
	from   gl_alloc_batches 
	where  chart_of_accounts_id = (select chart_of_accounts_id 
	                               from   gl_sets_of_books 
	                               where  upper(short_name) = upper('&1'));

	SELECT COUNT(1) 
	INTO l_total_imported_batch
	FROM   CRST_GL_ALLOC_BATCHES;
	
	select count(1)
	into   l_total_extracted_formulas
	from   gl_alloc_formulas f,
		   gl_alloc_batches b
	where  f.allocation_batch_id =b.allocation_batch_id
	and    b.chart_of_accounts_id = (select chart_of_accounts_id 
	                               	 from   gl_sets_of_books 
	                                 where  upper(short_name) = upper('&1')) ;
	
	SELECT COUNT(1) 
	INTO l_total_imported_formulas 
	FROM CRST_GL_ALLOC_FORMULAS;

    select count(1)
    into   l_total_extracted_flines
    from   gl_alloc_formula_lines l,
     	   gl_alloc_formulas f,
     	   gl_alloc_batches b
    where  l.allocation_formula_id = f.allocation_formula_id
    and	   f.allocation_batch_id = b.allocation_batch_id
	and    b.chart_of_accounts_id = (select chart_of_accounts_id 
	                               	 from   gl_sets_of_books 
	                                 where  upper(short_name) = upper('&1')); 
	
	select count(1)
    into   l_total_imported_flines
    from   CRST_GL_ALLOC_FORMULA_LINES;

	dbms_output.put_line('Function: '||l_func_id);		
	dbms_output.put_line('Total Extracted (Batch): '||TO_CHAR(l_total_extracted_batch)) ;
	dbms_output.put_line('Total Imported (Batch):  '||TO_CHAR(l_total_imported_batch));
	IF l_total_extracted_batch <> l_total_imported_batch THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
	
	dbms_output.put_line('Total Extracted (Formulas): '||TO_CHAR(l_total_extracted_formulas)) ;
	dbms_output.put_line('Total Imported (Formulas):  '||TO_CHAR(l_total_imported_formulas));
	IF l_total_extracted_formulas <> l_total_imported_formulas THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
						
	dbms_output.put_line('Total Extracted (Formulas Lines): '||TO_CHAR(l_total_extracted_flines)) ;
	dbms_output.put_line('Total Imported (Formulas Lines):  '||TO_CHAR(l_total_imported_flines));
	IF l_total_extracted_flines <> l_total_imported_flines THEN
		dbms_output.put_line('Error: No. of Records do not match!');
	END IF;		
	
EXCEPTION
	WHEN OTHERS THEN	
		dbms_output.put_line('Function Error: '||l_func_id);    

end;
/
